import os
import sys
import logging
import traceback
import sqlite3
import pandas as pd

class SQLite:
    def __init__(self, dbpath):
        if not os.path.isabs(dbpath):
            raise ValueError("数据库文件路径必须是绝对路径。")
        self.dbpath = dbpath
        with sqlite3.connect(self.dbpath) as conn:
            self._create_tables(conn)

    @staticmethod
    def get_table_schemas():
        return {
            "recipients": """
                CREATE TABLE IF NOT EXISTS recipients (
                    country_code TEXT PRIMARY KEY,
                    country_name TEXT NOT NULL,
                    yk TEXT,
                    bs TEXT,
                    eu TEXT,
                    hq2 TEXT
                )
            """,
            "SPBInformation": """
                CREATE TABLE IF NOT EXISTS SPBInformation (
                    Item TEXT PRIMARY KEY, 
                    ProductPlanner TEXT NOT NULL
                )
            """,
            "CertificateCode": """
                CREATE TABLE IF NOT EXISTS CertificateCode (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Item TEXT NOT NULL,
                    CertificateCode TEXT NOT NULL
                )
            """
        }

    def create_table_if_not_exists(self, conn, table_name, schema):
        cursor = conn.cursor()
        cursor.execute(schema)

    def _create_tables(self, conn):
        for table_name, schema in self.get_table_schemas().items():
            self.create_table_if_not_exists(conn, table_name, schema)

    def connect(self):
        return sqlite3.connect(self.dbpath)



    # 使用事务批量删除
    def delete_from_sqlite_with_transaction(self, df, table_name, column):
        """
        使用事务批量删除SQLite数据库中的记录。
        参数：
        df: DataFrame对象，包含要删除的记录的标识。
        table_name: 数据库中的表名。
        column: 要匹配的列名。
        """
        with self.connect() as conn:
            cursor = conn.cursor()
            # 开始一个新的事务
            cursor.execute("BEGIN TRANSACTION;")
            try:
                # 遍历DataFrame对象，删除每一行对应的记录
                for index, row in df.iterrows():
                    # 构造SQL语句
                    sql_delete = f"DELETE FROM {table_name} WHERE {column} = ?"
                    # 执行SQL语句
                    cursor.execute(sql_delete, (row[column],))
                # 提交事务
                conn.commit()
            except Exception as e:
                # 回滚事务
                conn.rollback()

    # 查询内存数据库中的数据，如果查询成功，返回DataFrame，否则返回None
    def query(self, sql, params=None):
        with self.connect() as conn:
            if params:
                return pd.read_sql_query(sql, conn, params=params)
            else:
                return pd.read_sql_query(sql, conn)

    # 将DataFrame批量导入到数据库中，减少数据库操作次数，可以提高数据导入效率，如果导入成功，返回True，否则返回False
    def insert_dataframe(self, df, table_name):
        if df.empty:
            return False  # 如果DataFrame为空，则不进行插入操作

        with self.connect() as conn:
            cur = conn.cursor()
            try:
                conn.execute("BEGIN TRANSACTION")  # 开始事务
                placeholders = ', '.join(['?' for _ in range(len(df.columns))])
                columns = ', '.join(df.columns)
                sql = f'INSERT INTO {table_name}({columns}) VALUES ({placeholders})'
                cur.executemany(sql, df.values.tolist())
                conn.commit()

                if cur.rowcount != len(df):  # 检查实际插入的行数是否与DataFrame的行数相等
                    raise RuntimeError("Not all rows were inserted successfully. Rolling back transaction.")
            except Exception as e:
                conn.rollback()  # 回滚事务
                base_path = os.path.dirname(os.path.realpath(sys.argv[0]))
                log_path = os.path.join(base_path, "log.log")
                # 获取异常信息
                tb = traceback.format_exc()
                # 创建一个logger
                logger = logging.getLogger('my_logger')
                # 设置日志级别
                logger.setLevel(logging.ERROR)

                # 创建一个handler用于写入日志文件
                fh = logging.FileHandler(log_path, encoding='utf-8')

                # 定义handler的输出格式
                formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
                fh.setFormatter(formatter)
                # 给logger添加handler
                logger.addHandler(fh)
                # 记录异常
                logger.error(f"An exception occurred: {tb}")
                return False
        return True

    # 执行单条SQL语句
    def Execute_one_SQL(self,sql):
        with self.connect() as conn:
            cursor = conn.cursor()
            try:
                cursor.execute(sql)
                conn.commit()
                return True
            except Exception as e:
                conn.rollback()
                return False

    def update_isscan_batch(self, sql_list):
        """
        批量修改isscan字段。

        参数：
        sql_list: 包含多条修改语句的 SQL 语句列表。
        """
        with self.connect() as conn:
            cursor = conn.cursor()
            cursor.execute("BEGIN TRANSACTION;")
            try:
                for sql in sql_list:
                    cursor.execute(sql)
                    # if cursor.rowcount != 1:  # 检查是否成功修改了一条记录
                    #     raise RuntimeError("Failed to update one or more records. Rolling back transaction.")
                conn.commit()
                return True
            except Exception as e:
                conn.rollback()
                return False

    def alter_table_drop_column(self, table_name, column_name):
        """
        从表中删除列。

        参数：
        table_name: 要修改的表名。
        column_name: 要删除的列名。
        """
        with self.connect() as conn:
            cursor = conn.cursor()
            try:
                cursor.execute(f"ALTER TABLE {table_name} DROP COLUMN {column_name};")
                conn.commit()
                return True
            except Exception as e:
                conn.rollback()
                return False

    def alter_table_add_column(self, table_name, column_name, column_type):
        """
        向表中添加新列。

        参数：
        table_name: 要修改的表名。
        column_name: 要添加的列名。
        column_type: 新列的数据类型。
        """
        with self.connect() as conn:
            cursor = conn.cursor()
            try:
                cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_type};")
                conn.commit()
                return True
            except Exception as e:
                conn.rollback()
                return False

